package com.mars.admin.common.utils;

import cn.hutool.db.Entity;
import cn.hutool.db.SqlRunner;
import cn.hutool.db.ds.DSFactory;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;
import java.util.Random;

public class Coordinategen {

    /**
     * @Title: randomLonLat
     * @Description: 在矩形内随机生成经纬度
     * @param MinLon：最新经度  MaxLon： 最大经度   MinLat：最新纬度   MaxLat：最大纬度    type：设置返回经度还是纬度
     * @return
     * @throws
     */
    public String randomLonLat(double MinLon, double MaxLon, double MinLat, double MaxLat) {
        Random random = new Random();
        BigDecimal db = new BigDecimal(Math.random() * (MaxLon - MinLon) + MinLon);
        String lon = db.setScale(6, BigDecimal.ROUND_HALF_UP).toString();// 小数后6位
        db = new BigDecimal(Math.random() * (MaxLat - MinLat) + MinLat);
        String lat = db.setScale(6, BigDecimal.ROUND_HALF_UP).toString();
        System.out.println(lon+","+lat);
        return lon+","+lat;
    }

    public void insertRandomSensor() {

        String coord = randomLonLat(116.465737,116.549099,39.914497,39.975566);
        String[] coordstr = coord.split(",");

        Random r3 = new Random();

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);
        Entity entity = Entity.create("sensor_info");
        entity.set("sensor_longitude",coordstr[0]);
        entity.set("sensor_latitude",coordstr[1]);
        entity.set("sensor_location","北京市东城区XXX街道");
        entity.set("sensor_battnum",r3.nextInt(2)+1);
        entity.set("sensor_ladder","T01");
        String screwcode = "T" + String.format("%02d",r3.nextInt(9)+3);
        entity.set("sensor_screwdriver",screwcode);
        entity.set("sensor_batttype","B0"+r3.nextInt(5)+1);
        entity.set("change_difficulty",r3.nextInt(10)+1);
        String toolcode = "T" + String.format("%02d",r3.nextInt(12)+13);
        entity.set("sensor_tool1",toolcode);

        try {
            runner.insert(entity);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Entity> getSensorNeedfix(String username) {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select a.id,a.sensor_longitude,a.sensor_latitude,a.sensor_location,b.battery_status,d.code_name  from sensor_info a,sensor_needfix b,sensor_dict d where a.id = b.sensor_id and a.sensor_type = d.code_value and d.code_type = 'sensor_type' and b.change_status = '0' and b.user_name = ? order by b.battery_status;", username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public List<Entity> getUser(String username) {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select id,realname,whgroup from service_engineer where username =?", username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public List<Entity> getBattery(String username) {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select a.code_name,sum(b.battnum) battnums from sensor_dict a,sensor_needfix b where a.code_type='sensor_batttype' and a.code_value = b.sensor_batttype and b.user_name=? group by a.code_name;", username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public List<Entity> getToolist(String username) {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select c.code_name from sensor_dict c,(select distinct a.sensor_ladder tool_type from sensor_info a,sensor_needfix b where a.id = b.sensor_id and b.user_name = ?\n" +
                    "union\n" +
                    "select distinct a.sensor_tool1 tool_type from sensor_info a,sensor_needfix b where a.id = b.sensor_id and b.user_name = ?\n" +
                    "union\n" +
                    "select distinct a.sensor_tool2 tool_type from sensor_info a,sensor_needfix b where a.id = b.sensor_id and b.user_name = ?) d\n" +
                    "where c.code_type = 'sensor_tool' and c.code_value = d.tool_type;", username,username,username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public List<Entity> getAllToolist() {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select code_name from sensor_dict where code_type = 'sensor_tool';");
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public List<Entity> getAllBattlist() {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        List<Entity>  entities = null;
        try {
            entities = runner.query("select code_name from sensor_dict where code_type = 'sensor_batttype';");
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  entities;
    }

    public int updateBattstatus(String username,String sensorid) {

        DataSource ds = DSFactory.get();
        SqlRunner runner = SqlRunner.create(ds);

        int updaterows = 0;

        try {
            updaterows = runner.update(
                    Entity.create().set("change_status", "1"),
                    Entity.create("sensor_needfix").set("user_name", username).set("sensor_id",new Integer(sensorid))
            );
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        System.out.println(entities.get(0).getBigDecimal("sensor_longitude"));
        return  updaterows;
    }

    public static void main(String[] args) {

        Coordinategen coordinategen = new Coordinategen();

        coordinategen.getSensorNeedfix("test1");
//        for (int i=0;i<1000;i++) {
//            coordinategen.insertRandomSensor();
//        }

    }
}
